/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistencia;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.text.StyledEditorKit;

/**
 *
 * @author Angelo
 */
public class FacadePersistence {

    private Connection conn;

    public FacadePersistence() {
    }

    public void connect(String usuario, String senha, String host, int porta, String sid) throws SQLException, ClassNotFoundException {
        this.conn = new ConnectionDAO(usuario, senha, host, porta, sid).getConnection();
    }
    
    public List<String> getOwners() throws SQLException {
        List<String> l = new ArrayList<String>();
        String sql, r;

        sql = "SELECT owner FROM all_tables group by owner";

        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) {
            r = rst.getString("OWNER");
            l.add(r);
        }
        stmt.close();
        return l;
    }
    
    public boolean createStructure() throws SQLException
    {
        try
        {
            //--cria tipo t_column
            String query_t_collum = "CREATE OR REPLACE TYPE t_column AS OBJECT ( column_name VARCHAR2(30),data_type VARCHAR2(30),data_length INTEGER, data_precision INTEGER, nullable VARCHAR2(1), par_avg_size NUMBER(8,2), par_percent_null NUMBER(8,2))";
            PreparedStatement t_collum = conn.prepareStatement(query_t_collum);
            t_collum.execute();

            //--cria tipo t_columns como tabela do tipo t_column
            String query_t_collums = "CREATE OR REPLACE TYPE t_columns AS TABLE OF t_column";
            PreparedStatement t_collums = conn.prepareStatement(query_t_collums);
            t_collums.execute();


            //--cria tipo t_table que contem t_columns (uma lista de colunas)
            String query_t_table = "CREATE OR REPLACE TYPE t_table AS OBJECT (  table_name VARCHAR2(30),column_list t_columns, tablespace VARCHAR2(100), par_records INTEGER,par_growth_rate NUMBER(8,2),par_retention_time INTEGER  )";
            PreparedStatement t_table = conn.prepareStatement(query_t_table);
            t_table.execute();

            //-- cria tipo t_tables como tabela do tipo t_table
            String query_t_tables = "CREATE OR REPLACE TYPE t_tables AS TABLE OF t_table";
            PreparedStatement t_tables = conn.prepareStatement(query_t_tables);
            t_tables.execute();


            //-- cria tipo t_owner
            String query_t_owner = "CREATE OR REPLACE TYPE t_owner AS OBJECT (  owner VARCHAR(30),table_list t_tables)";
            PreparedStatement t_owner = conn.prepareStatement(query_t_owner);
            t_owner.execute();

            //-- única tabela do sistema, que armazena tudo sobre um owner
            String query_owner = "CREATE TABLE owners OF t_owner (owner not null) NESTED TABLE table_list STORE AS ntab_owner_tables ( NESTED TABLE column_list STORE AS ntab_table_columns )";
            PreparedStatement owner = conn.prepareStatement(query_owner);
            owner.execute();
            
            //--cria procedure para carga dos dados.
            String query_procedure_insere_owner = "CREATE OR REPLACE PROCEDURE P_INSERE_OWNER (p_owner VARCHAR2) IS  BEGIN INSERT INTO OWNERS VALUES (T_OWNER(p_owner,T_TABLES())); FOR c_table IN (SELECT * FROM all_tables where owner=p_owner) LOOP INSERT INTO TABLE(SELECT O.TABLE_LIST FROM OWNERS O WHERE O.OWNER=p_owner) T VALUES (T_TABLE(c_table.table_name,T_COLUMNS(),c_table.tablespace_name,c_table.num_rows,0,5)); FOR c_column IN (SELECT * FROM all_tab_columns a where a.owner=p_owner and a.table_name = c_table.table_name)    LOOP INSERT INTO TABLE(SELECT T.COLUMN_LIST FROM TABLE(SELECT O.TABLE_LIST FROM OWNERS O WHERE O.OWNER=p_owner) T WHERE T.TABLE_NAME = c_column.table_name) VALUES (T_COLUMN(c_column.column_name, c_column.data_type, c_column.data_length, c_column.data_precision, c_column.nullable, c_column.data_length, 0)); END LOOP; END LOOP; COMMIT; END;";
            PreparedStatement procedure_insere_owner = conn.prepareStatement(query_procedure_insere_owner);
            procedure_insere_owner.execute();
                        
            //scripts vladimir inicio ##############################################################################################################
                    //T_VALOR_BRUTO
                    String q_T_VALOR_BRUTO = "CREATE TABLE T_VALOR_BRUTO (table_name varchar2(100), column_name varchar2(100), used_bytes number, alloc_bytes number, par_percent_null number)";
                    PreparedStatement T_VALOR_BRUTO = conn.prepareStatement(q_T_VALOR_BRUTO);
                    T_VALOR_BRUTO.execute();
                    
                    //FN_CALCULA_INITIAL
                    String q_FN_CALCULA_INITIAL = "CREATE OR REPLACE FUNCTION FN_CALCULA_INITIAL(V_SIZE IN NUMBER, V_PERC IN NUMBER) RETURN NUMBER IS  V_SIZE_INITIAL NUMBER; V_SIZE_TEMP NUMBER; BEGIN V_SIZE_TEMP := V_SIZE; FOR COUNT IN 1..2 LOOP V_SIZE_TEMP := V_SIZE_TEMP + (V_SIZE_TEMP * (V_PERC / 100)); END LOOP; V_SIZE_INITIAL := V_SIZE_TEMP; RETURN V_SIZE_INITIAL; END;";
                    PreparedStatement FN_CALCULA_INITIAL = conn.prepareStatement(q_FN_CALCULA_INITIAL);
                    FN_CALCULA_INITIAL.execute();
                    
                    
                    //FN_CALCULA_NEXT
                    String q_FN_CALCULA_NEXT = "CREATE OR REPLACE FUNCTION FN_CALCULA_NEXT(V_SIZE IN NUMBER, V_PERC IN NUMBER, V_TIME NUMBER) RETURN NUMBER IS  V_SIZE_NEXT NUMBER; V_SIZE_TEMP NUMBER; BEGIN V_SIZE_TEMP := V_SIZE; FOR COUNT IN 1..V_TIME + 1 LOOP V_SIZE_TEMP := V_SIZE_TEMP + (V_SIZE_TEMP * (V_PERC / 100)); END LOOP; V_SIZE_NEXT := V_SIZE_TEMP; RETURN V_SIZE_NEXT; END;";
                    PreparedStatement FN_CALCULA_NEXT = conn.prepareStatement(q_FN_CALCULA_NEXT);
                    FN_CALCULA_NEXT.execute();
                    
                    //P_CALCULA_OCUPACAO_INICIAL
                    String q_P_CALCULA_OCUPACAO_INICIAL = "create or replace procedure P_CALCULA_OCUPACAO_INICIAL (v_table_name varchar2, v_lines number) is begin declare  ub NUMBER; ab NUMBER; v_column_name varchar2(50); v_data_type   varchar2(50); v_data_length number; v_par_percent_null number; v_tablespace_name varchar2(50); cl sys.create_table_cost_columns; cursor v_column_information is select c.column_name, c.data_type, c.data_length, c.par_percent_null from table(select t.column_list  from owners o, table(o.table_list) t where t.table_name= v_table_name) c; begin select t.tablespace into v_tablespace_name from owners o, table(o.table_list) t where t.table_name = v_table_name; open v_column_information; loop fetch v_column_information into v_column_name, v_data_type, v_data_length, v_par_percent_null; exit when v_column_information%NOTFOUND; cl := sys.create_table_cost_columns(sys.create_table_cost_colinfo(v_data_type,v_data_length)); dbms_space.create_table_cost(v_tablespace_name,cl,v_lines,0,ub,ab); insert into T_VALOR_BRUTO(table_name, column_name,used_bytes, alloc_bytes, par_percent_null) values (v_table_name, v_column_name, ub, ab, v_par_percent_null); commit; end loop; close v_column_information; end; end;";
                    PreparedStatement P_CALCULA_OCUPACAO_INICIAL = conn.prepareStatement(q_P_CALCULA_OCUPACAO_INICIAL);
                    P_CALCULA_OCUPACAO_INICIAL.execute();
            //scripts vladimir fim #################################################################################################################
            
            
            return true;
        }
        catch (SQLException ex)
        {
            dropStructure();
            throw ex;
        }
    }    
    
    public boolean reCreateStructure(String owner) throws SQLException
    {
        if(hasStructure(owner))
        {
            dropStructure();
        }
        createStructure();
        return true;
    }
    
    public boolean hasStructure(String owner) throws SQLException
    {
        StringBuilder st = new StringBuilder();
        st.append("SELECT * FROM ALL_ALL_TABLES where owner = '"+owner+"' AND table_name = 'OWNERS'");
        
        PreparedStatement stmt = conn.prepareStatement(st.toString());        
        int result  = stmt.executeUpdate();
        return (result > 0);
    }
    
    private void dropStructure()
    {
        PreparedStatement stmt;
        try 
        {
            stmt = conn.prepareStatement("drop table owners");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        try 
        {
            stmt = conn.prepareStatement("drop type t_owner");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        try 
        {
            stmt = conn.prepareStatement("drop type t_tables");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        try 
        {
            stmt = conn.prepareStatement("drop type t_table");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
         try 
        {
            stmt = conn.prepareStatement("drop type t_columns");
            stmt.execute();
        } 
        catch (SQLException ex) { }
         
        try 
        {
            stmt = conn.prepareStatement("drop type t_column");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        
        try 
        {
            stmt = conn.prepareStatement("drop PROCEDURE P_INSERE_OWNER");
            stmt.execute();
        } 
        catch (SQLException ex) { }
        
        //scripts vladimir inicio ############################################################################################################## 
            try 
            {
                stmt = conn.prepareStatement("drop table T_VALOR_BRUTO");
                stmt.execute();
            } 
            catch (SQLException ex) { }
            
            
            try 
            {
                stmt = conn.prepareStatement("drop function FN_CALCULA_INITIAL");
                stmt.execute();
            } 
            catch (SQLException ex) { }
            
            try 
            {
                stmt = conn.prepareStatement("drop function FN_CALCULA_NEXT");
                stmt.execute();
            } 
            catch (SQLException ex) { }
            
            try 
            {
                stmt = conn.prepareStatement("drop procedure P_CALCULA_OCUPACAO_INICIAL");
                stmt.execute();
            } 
            catch (SQLException ex) { }
            

        //scripts vladimir fim #################################################################################################################
    }
    
    public void setOwner(String owner) throws SQLException
    {    
        if(!hasOwner(owner))
        {
            String queryCall_P_INSERE_OWNER = "{CALL P_INSERE_OWNER(?)}";
            CallableStatement cs = conn.prepareCall(queryCall_P_INSERE_OWNER);
            cs.setString(1, owner);
            cs.execute();      
            cs.close();
        }
    }
    
    private boolean hasOwner(String owner) throws SQLException
    {
        String queryHasOwner = "select count(o.owner) as count from owners o where owner = '"+owner+"'";
        PreparedStatement stmt = conn.prepareStatement(queryHasOwner);
        ResultSet result = stmt.executeQuery();
        
        while (result.next()) 
        {
            int count = result.getInt("count");
            return (count > 0);
        }
        return false;
    }



    public List<String> getTables(String owner) throws SQLException {
        List<String> l = new ArrayList<String>();
        String sql, r;

        sql = "select "
                + "t.table_name, "
                + "t.par_records, "
                + "t.par_growth_rate, "
                + "t.par_retention_time "
                + "from "
                + "table("
                + "select distinct "
                + "o.table_list "
                + "from "
                + "owners o "
                + "where o.owner=?) t";

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) {
            r = rst.getString("TABLE_NAME") + ";";
            r = r + rst.getString("PAR_RECORDS") + ";";
            r = r + rst.getString("PAR_GROWTH_RATE") + ";";
            r = r + rst.getString("PAR_RETENTION_TIME") + ";";
            l.add(r);
        }
        stmt.close();
        return l;
    }
    

    public List<String> getColumns(String owner, String table) throws SQLException {
        List<String> l = new ArrayList<String>();
        String sql, r;

        sql = "select "
                + "c.column_name, "
                + "c.data_type, "
                + "c.data_length, "
                + "c.data_precision, "
                + "c.nullable, "
                + "c.par_avg_size, "
                + "c.par_percent_null "
                + "from "
                + "table("
                + "select "
                + "t.column_list "
                + "from "
                + "owners o, "
                + "table(o.table_list) t "
                + "where "
                + "o.owner=? "
                + "and "
                + "t.table_name=?) c";


        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);
        stmt.setString(2, table);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) {
            r = rst.getString("COLUMN_NAME") + ";";
            r = r + rst.getString("DATA_TYPE") + ";";
            r = r + rst.getString("DATA_LENGTH") + ";";
            r = r + rst.getString("DATA_PRECISION") + ";";
            r = r + rst.getString("NULLABLE") + ";";
            r = r + rst.getString("PAR_AVG_SIZE") + ";";
            r = r + rst.getString("PAR_PERCENT_NULL") + ";";
            l.add(r);
        }
        stmt.close();
        return l;
    }

    public boolean writeDataTables(String owner, String table, int records, double growth, int retention) throws SQLException {
        String sql;
        
        sql = "update "
                + "table("
                    + "select "
                        + "o.table_list "
                    + "from "
                        + "owners o "
                    + "where "
                        + "o.owner=?) t "
                + "set "
                    + "t.par_records=?, "
                    + "t.par_growth_rate=?, "
                    + "t.par_retention_time=? "
                + "where "
                    + "t.table_name=?";                                
                
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);       
        stmt.setInt(2, records);
        stmt.setDouble(3, growth);
        stmt.setInt(4, retention);
        stmt.setString(5, table);
        boolean result = stmt.execute();
        stmt.close();
        return result;
    }

    public boolean writeDataColumns(String owner, String table, String column, double avg, double percent) throws SQLException {
        String sql;
        sql = "update "
                + "table("
                    + "select "
                    + "t.column_list "
                    + "from "
                        + "owners o, "
                        + "table(o.table_list) t "
                    + "where "
                        + "o.owner=? "
                        + "and "
                        + "t.table_name=?) c "
                + "set "                
                + "c.par_avg_size=?, "
                + "c.par_percent_null=? "
                + "where c.column_name=?";                
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, owner);
        stmt.setString(2, table);
        stmt.setDouble(3, avg);
        stmt.setDouble(4, percent);
        stmt.setString(5, column);
        
        boolean result = stmt.execute();
        stmt.close();
        return result;
    }
    
//    public void restultCalc(String owner)
//    {
//        //chama procedures do banco e retorna o resultado da simulação.
//    }
    
    public List<String> restultCalc() throws SQLException
    {
        List<String> resultCalc = new ArrayList<String>();        
        String queryResultCalc = "SELECT TABLE_NAME NOME, SUM(ALLOC_BYTES) BRUTO, SUM (USED_BYTES) LIQUIDO, FN_CALCULA_INITIAL(SUM(ALLOC_BYTES - (ALLOC_BYTES * (PAR_PERCENT_NULL/100))), 40) INITIAL_BRUTO, FN_CALCULA_INITIAL(SUM(USED_BYTES - (USED_BYTES * (PAR_PERCENT_NULL/100))), 40) INITIAL_LIQUIDO, FN_CALCULA_NEXT(SUM(ALLOC_BYTES - (ALLOC_BYTES * (PAR_PERCENT_NULL/100))), 40, 8) NEXT_BRUTO, FN_CALCULA_NEXT(SUM(USED_BYTES - (USED_BYTES * (PAR_PERCENT_NULL / 100))), 40, 8) NEXT_LIQUIDO FROM T_VALOR_BRUTO GROUP BY TABLE_NAME";
        PreparedStatement stmt = conn.prepareStatement(queryResultCalc);
        ResultSet rst = stmt.executeQuery();

        while (rst.next()) 
        {
            StringBuilder st = new StringBuilder(7);
            st.append(rst.getString("NOME"));
            st.append(";");
            st.append(rst.getDouble("BRUTO"));
            st.append(";");
            st.append(rst.getDouble("LIQUIDO"));
            st.append(";");
            st.append(rst.getDouble("INITIAL_BRUTO"));
            st.append(";");
            st.append(rst.getDouble("INITIAL_LIQUIDO"));
            st.append(";");
            st.append(rst.getDouble("NEXT_BRUTO"));
            st.append(";");
            st.append(rst.getDouble("NEXT_LIQUIDO"));
            st.append(";");
            resultCalc.add(st.toString());
        }
        stmt.close();
        return resultCalc;
    }
    
    
    public void cleanTablesOfValumetricCalculation() throws SQLException
    {
        String queryTruncateT_VALOR_BRUTO = "truncate table T_VALOR_BRUTO";
        PreparedStatement stmt = conn.prepareStatement(queryTruncateT_VALOR_BRUTO);
        stmt.execute();
    }
    
    public void calcInitialOcupation(String table_name, int numberOfLines) throws SQLException 
    {
        String queryCALL_P_CALCULA_OCUPACAO_INICIAL = "{CALL P_CALCULA_OCUPACAO_INICIAL (?, ?)}";
        CallableStatement cs = conn.prepareCall(queryCALL_P_CALCULA_OCUPACAO_INICIAL);
        cs.setString(1, table_name);
        cs.setInt(2, numberOfLines);
        cs.execute();      
        cs.close();
    }
//    public List<String> getTables(String owner) throws SQLException {
//        List<String> l = new ArrayList<String>();
//        String sql, r;
//
//        sql = "SELECT table_name FROM all_tables WHERE owner=?";
//
//        PreparedStatement stmt = conn.prepareStatement(sql);
//        stmt.setString(1, owner);
//        ResultSet rst = stmt.executeQuery();
//
//        while (rst.next()) {
//            r = rst.getString("TABLE_NAME");
//            l.add(r);
//        }
//        return l;
//    }
//
//    public List<String> getColumns(String owner, String tabela) throws SQLException {
//        List<String> l = new ArrayList<String>();
//        String sql, r;
//
//        sql = "select "
//                + "column_name, "
//                + "data_type, "
//                + "data_length, "
//                + "data_precision, "
//                + "nullable "
//                + "from "
//                + "all_tab_columns "
//                + "where "
//                + "owner=? "
//                + "and "
//                + "table_name=?";
//
//        PreparedStatement stmt = conn.prepareStatement(sql);
//        stmt.setString(1, owner);
//        stmt.setString(2, tabela);
//        ResultSet rst = stmt.executeQuery();
//
//        while (rst.next()) {
//            r = rst.getString("COLUMN_NAME") + ";";
//            r = r + rst.getString("DATA_TYPE") + ";";
//            r = r + rst.getString("DATA_LENGTH") + ";";
//            r = r + rst.getString("DATA_PRECISION") + ";";
//            r = r + rst.getString("NULLABLE") + ";";
//            l.add(r);
//        }
//        return l;
//    }    

    
}
